Oracle / PLSQL: REGEXP

您所在的位置：网站首页 › regexp replace › Oracle / PLSQL: REGEXP

Oracle / PLSQL: REGEXP

#Oracle / PLSQL: REGEXP| 来源: 网络整理| 查看: 265

Oracle / PLSQL: REGEXP_REPLACE Function

This Oracle tutorial explains how to use the Oracle/PLSQL REGEXP_REPLACE function with syntax and examples.

Description

The Oracle/PLSQL REGEXP_REPLACE function is an extension of the REPLACE function. This function, introduced in Oracle 10g, will allow you to replace a sequence of characters in a string with another set of characters using regular expression pattern matching.

Syntax

The syntax for the REGEXP_REPLACE function in Oracle is:

REGEXP_REPLACE( string, pattern [, replacement_string [, start_position [, nth_appearance [, match_parameter ] ] ] ] ) Parameters or Arguments string The string to search. It can be CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. pattern

The regular expression matching information. It can be a combination of the following:

Value Description ^ Matches the beginning of a string. If used with a match_parameter of 'm', it matches the start of a line anywhere within expression. $ Matches the end of a string. If used with a match_parameter of 'm', it matches the end of a line anywhere within expression. * Matches zero or more occurrences. + Matches one or more occurrences. ? Matches zero or one occurrence. . Matches any character except NULL. | Used like an "OR" to specify more than one alternative. [ ] Used to specify a matching list where you are trying to match any one of the characters in the list. [^ ] Used to specify a nonmatching list where you are trying to match any character except for the ones in the list. ( ) Used to group expressions as a subexpression. {m} Matches m times. {m,} Matches at least m times. {m,n} Matches at least m times, but no more than n times. \n n is a number between 1 and 9. Matches the nth subexpression found within ( ) before encountering \n. [..] Matches one collation element that can be more than one character. [::] Matches character classes. [==] Matches equivalence classes. \d Matches a digit character. \D Matches a nondigit character. \w Matches a word character. \W Matches a nonword character. \s Matches a whitespace character. \S matches a non-whitespace character. \A Matches the beginning of a string or matches at the end of a string before a newline character. \Z Matches at the end of a string. *? Matches the preceding pattern zero or more occurrences. +? Matches the preceding pattern one or more occurrences. ?? Matches the preceding pattern zero or one occurrence. {n}? Matches the preceding pattern n times. {n,}? Matches the preceding pattern at least n times. {n,m}? Matches the preceding pattern at least n times, but not more than m times. replacement_string Optional. Matched patterns will be replaced with replacement_string in string. If the replacement_string parameter is omitted, the function simply removes all matched patterns, and returns the resulting string. start_position Optional. It is the position in string where the search will start. If omitted, it defaults to 1 which is the first position in the string. nth_appearance Optional. It is the nth appearance of pattern in string. If omitted, it defaults to 1 which is the first appearance of pattern in string. If you specify 0 for this parameter, all appearances of pattern will be replaced in string. match_parameter

Optional. It allows you to modify the matching behavior for the REGEXP_REPLACE function. It can be a combination of the following:

Value Description 'c' Perform case-sensitive matching. 'i' Perform case-insensitive matching. 'n' Allows the period character (.) to match the newline character. By default, the period is a wildcard. 'm' expression is assumed to have multiple lines, where ^ is the start of a line and $ is the end of a line, regardless of the position of those characters in expression. By default, expression is assumed to be a single line. 'x' Whitespace characters are ignored. By default, whitespace characters are matched like any other character. Returns

The REGEXP_REPLACE function returns a string value.

Note If there are conflicting values provided for match_parameter, the REGEXP_REPLACE function will use the last value. See also the REPLACE function. Applies To

The REGEXP_REPLACE function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g Example - Match on First Word

Let's start by using the REGEXP_REPLACE function to replace the first word in a string.

For example:

SELECT REGEXP_REPLACE ('TechOnTheNet is a great resource', '^(\S*)', 'CheckYourMath') FROM dual; Result: 'CheckYourMath is a great resource'

This example will return 'CheckYourMath is a great resource' because it will start the match at the beginning of the string as specified by ^ and then find the first word as specified by (\S*). The function will then replace this first word with 'CheckYourMath'.

Example - Match on Digit Characters

Let's look next at how we would use the REGEXP_REPLACE function to match on a single digit character pattern.

For example:

SELECT REGEXP_REPLACE ('2, 5, and 10 are numbers in this example', '\d', '#') FROM dual; Result: '#, #, and ## are numbers in this example'

This example will replace all numeric digits in the string as specified by \d. It will replace the occurrences with a # character.

We could change our pattern to search for only two-digit numbers.

For example:

SELECT REGEXP_REPLACE ('2, 5, and 10 are numbers in this example', '(\d)(\d)', '#') FROM dual; Result: '2, 5, and # are numbers in this example'

This example will replace a number that has two digits side-by-side as specified by (\d)(\d). In this case, it will skip over the 2 and 5 numeric values and replace 10 with a # character.

Now, let's look how we would use the REGEXP_REPLACE function with a table column to replace two digit numbers.

For example:

SELECT REGEXP_REPLACE (address, '(\d)(\d)', 'TBD') FROM contacts;

In this example, we are going to replace all two-digit values from the address field in the contacts table with the value 'TBD'.

Example - Match on more than one alternative

The next example that we will look at involves using the | pattern. The | pattern is used like an "OR" to specify more than one alternative.

For example:

SELECT REGEXP_REPLACE ('Anderson', 'a|e|i|o|u', 'G') FROM dual; Result: 'AndGrsGn'

This example will return 'AndGrsGn' because it is searching for the first vowel (a, e, i, o, or u) in the string. Since we did not specify a match_parameter value, the REGEXP_REPLACE function will perform a case-sensitive search which means that the 'A' in 'Anderson' will not be matched.

We could modify our query as follows to perform a case-insensitive search as follows:

SELECT REGEXP_REPLACE ('Anderson', 'a|e|i|o|u', 'G', 1, 0, 'i') FROM dual; Result: 'GndGrsGn'

Now because we have provide a match_parameter of 'i', the query will replace 'A' in the string. This time, the 'A' in 'Anderson' will be found as a match. Notice also that we specified 0 as the 5th parameter so that all occurrences would be replaced.

Now, let's quickly show how you would use this function with a column.

So let's say we have a contact table with the following data:

contact_id last_name 1000 Anderson 2000 Smith 3000 Johnson

Now, let's run the following query:

SELECT contact_id, last_name, REGEXP_REPLACE (last_name, 'a|e|i|o|u', 'G', 1, 0, 'i') AS "New Name" FROM contacts;

These are the results that would be returned by the query:

contact_id last_name New Name 1000 Anderson GndGrsGn 2000 Smith SmGth 3000 Johnson JGhnsGn Example - Match on nth_occurrence

The next example that we will look at involves the nth_occurrence parameter. The nth_occurrence parameter allows you to select which occurrence of the pattern you wish to replace in the string.

First Occurrence

Let's look at how to replace the first occurrence of a pattern in a string.

For example:

SELECT REGEXP_REPLACE ('TechOnTheNet', 'a|e|i|o|u', 'Z', 1, 1, 'i') FROM dual; Result: 'TZchOnTheNet'

This example will replace the second character ('e') in 'TechOnTheNet' because it is replacing the first occurrence of a vowel (a, e, i, o, or u) in the string.

Second Occurrence

Next, we will extract for the second occurrence of a pattern in a string.

For example:

SELECT REGEXP_REPLACE ('TechOnTheNet', 'a|e|i|o|u', 'Z', 1, 2, 'i') FROM dual; Result: 'TechZnTheNet'

This example will replace the fifth character ('O') in 'TechOnTheNet' because it is replacing the second occurrence of a vowel (a, e, i, o, or u) in the string.

Third Occurrence

For example:

SELECT REGEXP_REPLACE ('TechOnTheNet', 'a|e|i|o|u', 'Z', 1, 3, 'i') FROM dual; Result: 'TechOnThZNet'

This example will replace the ninth character ('e') in 'TechOnTheNet' because it is replacing the third occurrence of a vowel (a, e, i, o, or u) in the string.

【本文地址】

Oracle / PLSQL: REGEXP

Oracle / PLSQL: REGEXP

今日新闻

推荐新闻